package rwt.server.db;

import java.sql.*;
import java.util.*;
import java.util.Date;
import java.util.regex.Pattern;

import org.eclipse.jetty.util.security.Credential.MD5;
import org.slf4j.*;

import rwt.server.RailServer;
import rwt.server.core.*;
import rwt.server.core.User.UserRole;

public class DbUtil
{
	private static final Logger logger = LoggerFactory.getLogger(DbUtil.class);
	
	private static final Pattern userNamePattern = Pattern.compile("\\w{3,16}");
	
	public static final String INVALID_USER_NAME_MESSAGE = "Only letters, numbers, and underscore " +
			"are permitted in user name, and it must be between 3 and 16 characters long.";
	
	private static long lastDatabaseModifiedTime = updateLastModifiedTime();
	
	public static long getLastModifiedTime()
	{
		return lastDatabaseModifiedTime;
	}
	
	public static long updateLastModifiedTime()
	{
		lastDatabaseModifiedTime = new Date().getTime() / 1000L * 1000L; // Truncate milliseconds
		return lastDatabaseModifiedTime;
	}
	
	public static boolean validateUserName(String userName)
	{
		return (userName != null && userNamePattern.matcher(userName).matches());
	}
	
	public static void createUser(Connection conn, User user) throws SQLException
	{
		if (!validateUserName(user.getUserName()))
			throw new SQLException(INVALID_USER_NAME_MESSAGE);
		
		updateLastModifiedTime();
		String insertUserSql = "INSERT INTO users (first_name, last_name, username, password)\n"
				+ "VALUES (?, ?, ?, ?)";
		try (PreparedStatement stmt = conn.prepareStatement(insertUserSql, Statement.RETURN_GENERATED_KEYS))
		{
			stmt.setString(1, user.getFirstName());
			stmt.setString(2, user.getLastName());
			stmt.setString(3, user.getUserName());
			stmt.setString(4, user.getPasswordHash());
			
			if (stmt.executeUpdate() == 0)
				throw new SQLException("Failed to create user: " + user);
			
			try (ResultSet generatedKeys = stmt.getGeneratedKeys())
			{
				if (generatedKeys.next())
				{
					user.setId(generatedKeys.getInt(1));
					logger.info("New user ID: " + user.getId());
				}
				else
				{
					throw new SQLException("Creating user failed, no generated key obtained.");
				}
			}
		}
		
		if (user.getId() == User.INVALID_ID)
			throw new SQLException("User '"+  user.getUserName() + "' does not have a valid ID.");
		
		String insertUserRolesSql = "INSERT INTO user_roles (user_id, role_id) VALUES (?, ?)";
		try (PreparedStatement stmt = conn.prepareStatement(insertUserRolesSql))
		{
			stmt.setInt(1, user.getId());
			stmt.setInt(2, user.getRole().getValue());
			
			if (stmt.executeUpdate() == 0)
				throw new SQLException("Failed to set user's role: " + user.getRole());
		}
	}
	
	public static List<User> getUsers(Connection conn) throws SQLException
	{
		List<User> users = new ArrayList<>();
		
		String selectSql = "SELECT id, first_name, last_name, username, role_id\n" + 
				"FROM users, user_roles WHERE users.id=user_roles.user_id";
		try (PreparedStatement stmt = conn.prepareStatement(selectSql))
		{
			try (ResultSet result = stmt.executeQuery())
			{
				while (result.next())
					users.add(userFromResultSet(result));
			}
		}
		return users;
	}
	
	public static User getUserByName(Connection conn, String userName) throws SQLException
	{
		String selectSql = "SELECT id, first_name, last_name, username, role_id\n" + 
				"FROM users, user_roles WHERE users.id=user_roles.user_id AND username=?";
		try (PreparedStatement stmt = conn.prepareStatement(selectSql))
		{
			stmt.setString(1, userName);
			try (ResultSet result = stmt.executeQuery())
			{
				if (result.next())
					return userFromResultSet(result);
			}
		}
		return null;
	}
	
	public static void deleteUser(Connection conn, int userId) throws SQLException
	{
		if (userId == User.INVALID_ID)
			throw new SQLException("Invaild user ID: " + userId);
		
		updateLastModifiedTime();
		String deleteSql = "DELETE FROM users WHERE id=?";
		try (PreparedStatement stmt = conn.prepareStatement(deleteSql))
		{
			stmt.setInt(1, userId);
			if (stmt.executeUpdate() < 1)
				throw new SQLException("User with ID " + userId + " not in system (not deleted).");
		}
		
		deleteSql = "DELETE FROM user_roles WHERE user_id=?";
		try (PreparedStatement stmt = conn.prepareStatement(deleteSql))
		{
			stmt.setInt(1, userId);
			stmt.executeUpdate();
		}
		
		logger.info("Deleted user with ID " + userId + ".");
	}
	
	public static void editUserRole(Connection conn, int userId, UserRole newRole) throws SQLException
	{
		updateLastModifiedTime();
		String updateSql = "UPDATE user_roles SET role_id=? WHERE user_id=?";
		try (PreparedStatement stmt = conn.prepareStatement(updateSql))
		{
			stmt.setInt(1, newRole.getValue());
			stmt.setInt(2, userId);
			stmt.executeUpdate();
		}
		
		logger.info("Updated role of user with ID " + userId + " to " + newRole.getName()+  ".");
	}
	
	public static boolean checkUserNameAvailable(Connection conn, String userName) throws SQLException
	{
		String selectSql = "SELECT id FROM users WHERE username=?";
		try (PreparedStatement stmt = conn.prepareStatement(selectSql))
		{
			stmt.setString(1, userName);
			try (ResultSet result = stmt.executeQuery())
			{
				return !result.next();
			}
		}
	}
	
	public static void createRoles(Connection conn) throws SQLException
	{
		updateLastModifiedTime();
		String insertSql = "INSERT INTO roles (id, name) VALUES (?, ?)";
		try (PreparedStatement stmt = conn.prepareStatement(insertSql))
		{
			for (UserRole userType : UserRole.values())
			{
				stmt.setInt(1, userType.getValue());
				stmt.setString(2, userType.getName());
				stmt.addBatch();
			}
			stmt.executeBatch();
		}
	}
	
	public static String md5(String input)
	{
		return MD5.digest(input);
	}
	
	private static User userFromResultSet(ResultSet result) throws SQLException
	{
		User user = new User();
		user.setId(result.getInt("id"));
		user.setFirstName(result.getString("first_name"));
		user.setLastName(result.getString("last_name"));
		user.setUserName(result.getString("username"));
		user.setRole(UserRole.fromInt(result.getInt("role_id")));
		return user;
	}
}
